﻿CREATE TABLE [Role]
(
	[Id]					UNIQUEIDENTIFIER	NOT NULL,
	[Name]					NVARCHAR(256)		NOT NULL,

	-- Common Entity Fields
	[DateCreated]			DATETIME			NOT NULL,
	[CreatedBy]				UNIQUEIDENTIFIER	NOT NULL,
	[DateLastUpdated]		DATETIME			NOT NULL,
	[LastUpdatedBy]			UNIQUEIDENTIFIER	NOT NULL,
	[State]					INT					NOT NULL,
	[Version]				INT					NOT NULL,
	[Checksum]				CHAR(64)			NOT NULL
)

INSERT INTO [Role] VALUES ('00000000-0000-0000-0000-000000000001', 'System', GetDate(), '00000000-0000-0000-0000-000000000001', GetDate(), '00000000-0000-0000-0000-000000000001', 0, 1, '')
INSERT INTO [Role] VALUES ('00000000-0000-0000-0000-000000000002', 'Administrator', GetDate(), '00000000-0000-0000-0000-000000000001', GetDate(), '00000000-0000-0000-0000-000000000001', 0, 1, '')
INSERT INTO [Role] VALUES ('00000000-0000-0000-0000-000000000003', 'Project Manager',  GetDate(), '00000000-0000-0000-0000-000000000001', GetDate(), '00000000-0000-0000-0000-000000000001', 0, 1, '')
INSERT INTO [Role] VALUES ('00000000-0000-0000-0000-000000000004', 'Developer',  GetDate(), '00000000-0000-0000-0000-000000000001', GetDate(), '00000000-0000-0000-0000-000000000001', 0, 1, '')
INSERT INTO [Role] VALUES ('00000000-0000-0000-0000-000000000005', 'Analyst',  GetDate(), '00000000-0000-0000-0000-000000000001', GetDate(), '00000000-0000-0000-0000-000000000001', 0, 1, '')
INSERT INTO [Role] VALUES ('00000000-0000-0000-0000-000000000006', 'Tester',  GetDate(), '00000000-0000-0000-0000-000000000001', GetDate(), '00000000-0000-0000-0000-000000000001', 0, 1, '')
INSERT INTO [Role] VALUES ('00000000-0000-0000-0000-000000000007', 'External',  GetDate(), '00000000-0000-0000-0000-000000000001', GetDate(), '00000000-0000-0000-0000-000000000001', 0, 1, '')

-- SELECT * FROM Role


  
CREATE TABLE [User]
(
	[Id]					UNIQUEIDENTIFIER	NOT NULL,
	[Name]					VARCHAR(256)		NOT NULL,
	[Pin]				    CHAR(4)				NOT NULL,
	[DisplayName]			NVARCHAR(256)		NOT NULL,
	[RoleId]				UNIQUEIDENTIFIER	NOT NULL,
	-- Common Entity Fields
	[DateCreated]			DATETIME			NOT NULL,
	[CreatedBy]				UNIQUEIDENTIFIER	NOT NULL,
	[DateLastUpdated]		DATETIME			NOT NULL,
	[LastUpdatedBy]			UNIQUEIDENTIFIER	NOT NULL,
	[State]					INT					NOT NULL,
	[Version]				INT					NOT NULL,
	[Checksum]				CHAR(64)			NOT NULL	
)

INSERT INTO [User] VALUES ('00000000-0000-0000-0000-000000000001', 'System', 'System', '00000000-0000-0000-0000-000000000001', GetDate(), '00000000-0000-0000-0000-000000000001', GetDate(), '00000000-0000-0000-0000-000000000001', 0, 1, '')

CREATE TABLE [TaskTemplate]
(
	[Id]					UNIQUEIDENTIFIER	NOT NULL,
	[Name]					VARCHAR(256)		NOT NULL,
	[ParentId]				UNIQUEIDENTIFIER	NOT NULL,
	[RoleId]				UNIQUEIDENTIFIER	NOT NULL,
	-- Common Entity Fields
	[DateCreated]			DATETIME			NOT NULL,
	[CreatedBy]				UNIQUEIDENTIFIER	NOT NULL,
	[DateLastUpdated]		DATETIME			NOT NULL,
	[LastUpdatedBy]			UNIQUEIDENTIFIER	NOT NULL,
	[State]					INT					NOT NULL,
	[Version]				INT					NOT NULL,
	[Checksum]				CHAR(64)			NOT NULL	
)

CREATE TABLE [Task]
(
	[Id]					UNIQUEIDENTIFIER	NOT NULL,
	[Name]					VARCHAR(256)		NOT NULL,
	[ParentId]				UNIQUEIDENTIFIER	NOT NULL,
	[RoleId]				UNIQUEIDENTIFIER	NOT NULL,
	[AssignedToId]			UNIQUEIDENTIFIER	NOT NULL,
	[Status]				VARCHAR(32)			NOT NULL, -- New, InProgress, Paused, Suspended, Cancelled, Completed
	-- Common Entity Fields
	[DateCreated]			DATETIME			NOT NULL,
	[CreatedBy]				UNIQUEIDENTIFIER	NOT NULL,
	[DateLastUpdated]		DATETIME			NOT NULL,
	[LastUpdatedBy]			UNIQUEIDENTIFIER	NOT NULL,
	[State]					INT					NOT NULL,
	[Version]				INT					NOT NULL,
	[Checksum]				CHAR(64)			NOT NULL
)

CREATE TABLE [TaskData]
(
	[Id]					UNIQUEIDENTIFIER	NOT NULL,
	[TaskId]				UNIQUEIDENTIFIER	NOT NULL,
	[Title]					NVARCHAR(256)		NOT NULL,
	[AttachmentId]			UNIQUEIDENTIFIER	NULL, -- Reference to Blob
	-- Common Entity Fields
	[DateCreated]			DATETIME			NOT NULL,
	[CreatedBy]				UNIQUEIDENTIFIER	NOT NULL,
	[DateLastUpdated]		DATETIME			NOT NULL,
	[LastUpdatedBy]			UNIQUEIDENTIFIER	NOT NULL,
	[State]					INT					NOT NULL,
	[Version]				INT					NOT NULL,
	[Checksum]				CHAR(64)			NOT NULL
)

CREATE TABLE [EventLog]
(
	[Id]					UNIQUEIDENTIFIER	NOT NULL,
	[TaskId]				UNIQUEIDENTIFIER	NOT NULL,
	[Title]					NVARCHAR(256)		NOT NULL,
	[Description]			NVARCHAR(2048)		NULL,
	[AttachmentId]			UNIQUEIDENTIFIER	NULL, -- Reference to Blob
	-- Common Entity Fields
	[DateCreated]			DATETIME			NOT NULL,
	[CreatedBy]				UNIQUEIDENTIFIER	NOT NULL,
	[DateLastUpdated]		DATETIME			NOT NULL,
	[LastUpdatedBy]			UNIQUEIDENTIFIER	NOT NULL,
	[State]					INT					NOT NULL,
	[Version]				INT					NOT NULL,
	[Checksum]				CHAR(64)			NOT NULL
)

CREATE TABLE Blob
(
	[Id]					UNIQUEIDENTIFIER	NOT NULL,
	[FileName]				VARCHAR(256)		NOT NULL,
	[FileExtension]			VARCHAR(256)		NOT NULL,
	[ContentType]			VARCHAR(48)			NOT NULL,
	[ContentLength]			INT					NOT NULL,
	[Content]				VARBINARY(MAX)		NOT NULL,
	-- Common Entity Fields
	[DateCreated]			DATETIME			NOT NULL,
	[CreatedBy]				UNIQUEIDENTIFIER	NOT NULL,
	[DateLastUpdated]		DATETIME			NOT NULL,
	[LastUpdatedBy]			UNIQUEIDENTIFIER	NOT NULL,
	[State]					INT					NOT NULL,
	[Version]				INT					NOT NULL,
	[Checksum]				CHAR(64)			NOT NULL
)
